-- =============================================================================
-- 金融数据MCP服务数据库初始化脚本
-- =============================================================================

-- 创建数据库（如果不存在）
-- CREATE DATABASE financial_data;

-- 连接到数据库
\c financial_data;

-- 创建扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";
CREATE EXTENSION IF NOT EXISTS "btree_gin";

-- 设置时区
SET timezone = 'Asia/Shanghai';

-- =============================================================================
-- 创建枚举类型
-- =============================================================================

-- 数据同步状态枚举
CREATE TYPE sync_status AS ENUM (
    'pending',
    'running', 
    'completed',
    'failed',
    'cancelled'
);

-- 数据质量状态枚举
CREATE TYPE quality_status AS ENUM (
    'excellent',
    'good',
    'fair',
    'poor',
    'critical'
);

-- 任务优先级枚举
CREATE TYPE task_priority AS ENUM (
    'low',
    'normal',
    'high',
    'critical'
);

-- =============================================================================
-- 创建基础表
-- =============================================================================

-- 股票基础信息表
CREATE TABLE IF NOT EXISTS stock_basic (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    ts_code VARCHAR(20) NOT NULL UNIQUE,
    symbol VARCHAR(10) NOT NULL,
    name VARCHAR(100) NOT NULL,
    area VARCHAR(50),
    industry VARCHAR(100),
    market VARCHAR(20),
    list_date DATE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    version INTEGER DEFAULT 1,
    is_active BOOLEAN DEFAULT true
);

-- 股票日线数据表
CREATE TABLE IF NOT EXISTS stock_daily (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    ts_code VARCHAR(20) NOT NULL,
    trade_date DATE NOT NULL,
    open_price DECIMAL(10,3),
    high_price DECIMAL(10,3),
    low_price DECIMAL(10,3),
    close_price DECIMAL(10,3),
    pre_close DECIMAL(10,3),
    change_amount DECIMAL(10,3),
    pct_chg DECIMAL(8,4),
    vol BIGINT,
    amount DECIMAL(20,2),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    version INTEGER DEFAULT 1,
    sync_status sync_status DEFAULT 'pending',
    quality_score DECIMAL(3,2) DEFAULT 1.00,
    UNIQUE(ts_code, trade_date)
);

-- 指数基础信息表
CREATE TABLE IF NOT EXISTS index_basic (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    ts_code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    market VARCHAR(20),
    publisher VARCHAR(100),
    category VARCHAR(50),
    base_date DATE,
    base_point DECIMAL(10,2),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    version INTEGER DEFAULT 1,
    is_active BOOLEAN DEFAULT true
);

-- 指数日线数据表
CREATE TABLE IF NOT EXISTS index_daily (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    ts_code VARCHAR(20) NOT NULL,
    trade_date DATE NOT NULL,
    close_price DECIMAL(10,3),
    open_price DECIMAL(10,3),
    high_price DECIMAL(10,3),
    low_price DECIMAL(10,3),
    pre_close DECIMAL(10,3),
    change_amount DECIMAL(10,3),
    pct_chg DECIMAL(8,4),
    vol BIGINT,
    amount DECIMAL(20,2),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    version INTEGER DEFAULT 1,
    sync_status sync_status DEFAULT 'pending',
    quality_score DECIMAL(3,2) DEFAULT 1.00,
    UNIQUE(ts_code, trade_date)
);

-- 基金基础信息表
CREATE TABLE IF NOT EXISTS fund_basic (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    ts_code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    management VARCHAR(100),
    custodian VARCHAR(100),
    fund_type VARCHAR(50),
    found_date DATE,
    due_date DATE,
    list_date DATE,
    issue_date DATE,
    delist_date DATE,
    issue_amount DECIMAL(20,2),
    m_fee DECIMAL(8,4),
    c_fee DECIMAL(8,4),
    duration_year DECIMAL(8,4),
    p_value DECIMAL(20,2),
    min_amount DECIMAL(20,2),
    exp_return DECIMAL(8,4),
    benchmark VARCHAR(200),
    status VARCHAR(20),
    invest_type VARCHAR(50),
    type VARCHAR(50),
    trustee VARCHAR(100),
    purc_startdate DATE,
    redm_startdate DATE,
    market VARCHAR(20),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    version INTEGER DEFAULT 1,
    is_active BOOLEAN DEFAULT true
);

-- 基金净值数据表
CREATE TABLE IF NOT EXISTS fund_nav (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    ts_code VARCHAR(20) NOT NULL,
    ann_date DATE,
    nav_date DATE NOT NULL,
    unit_nav DECIMAL(10,4),
    accum_nav DECIMAL(10,4),
    accum_div DECIMAL(10,4),
    net_asset DECIMAL(20,2),
    total_netasset DECIMAL(20,2),
    adj_nav DECIMAL(10,4),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    version INTEGER DEFAULT 1,
    sync_status sync_status DEFAULT 'pending',
    quality_score DECIMAL(3,2) DEFAULT 1.00,
    UNIQUE(ts_code, nav_date)
);

-- 债券基础信息表
CREATE TABLE IF NOT EXISTS bond_basic (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    ts_code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    bond_type VARCHAR(50),
    market VARCHAR(20),
    list_date DATE,
    delist_date DATE,
    issue_date DATE,
    maturity_date DATE,
    par_value DECIMAL(10,2),
    issue_price DECIMAL(10,2),
    coupon_rate DECIMAL(8,4),
    pay_frequency INTEGER,
    credit_rating VARCHAR(10),
    issuer VARCHAR(100),
    guarantor VARCHAR(100),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    version INTEGER DEFAULT 1,
    is_active BOOLEAN DEFAULT true
);

-- 债券日线数据表
CREATE TABLE IF NOT EXISTS bond_daily (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    ts_code VARCHAR(20) NOT NULL,
    trade_date DATE NOT NULL,
    close_price DECIMAL(10,3),
    open_price DECIMAL(10,3),
    high_price DECIMAL(10,3),
    low_price DECIMAL(10,3),
    pre_close DECIMAL(10,3),
    change_amount DECIMAL(10,3),
    pct_chg DECIMAL(8,4),
    vol BIGINT,
    amount DECIMAL(20,2),
    yield DECIMAL(8,4),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    version INTEGER DEFAULT 1,
    sync_status sync_status DEFAULT 'pending',
    quality_score DECIMAL(3,2) DEFAULT 1.00,
    UNIQUE(ts_code, trade_date)
);

-- =============================================================================
-- 创建系统管理表
-- =============================================================================

-- 数据同步任务表
CREATE TABLE IF NOT EXISTS sync_tasks (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    task_name VARCHAR(100) NOT NULL,
    task_type VARCHAR(50) NOT NULL,
    cron_expression VARCHAR(100),
    priority task_priority DEFAULT 'normal',
    status sync_status DEFAULT 'pending',
    last_run_time TIMESTAMP WITH TIME ZONE,
    next_run_time TIMESTAMP WITH TIME ZONE,
    success_count INTEGER DEFAULT 0,
    failure_count INTEGER DEFAULT 0,
    total_records INTEGER DEFAULT 0,
    processed_records INTEGER DEFAULT 0,
    error_message TEXT,
    config JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    is_enabled BOOLEAN DEFAULT true
);

-- 数据质量监控表
CREATE TABLE IF NOT EXISTS data_quality_checks (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    table_name VARCHAR(100) NOT NULL,
    check_type VARCHAR(50) NOT NULL,
    check_date DATE NOT NULL,
    total_records INTEGER,
    valid_records INTEGER,
    invalid_records INTEGER,
    completeness_score DECIMAL(5,4),
    accuracy_score DECIMAL(5,4),
    consistency_score DECIMAL(5,4),
    timeliness_score DECIMAL(5,4),
    validity_score DECIMAL(5,4),
    overall_score DECIMAL(5,4),
    quality_status quality_status,
    issues JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 系统配置表
CREATE TABLE IF NOT EXISTS system_config (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    config_key VARCHAR(100) NOT NULL UNIQUE,
    config_value TEXT,
    config_type VARCHAR(50) DEFAULT 'string',
    description TEXT,
    is_encrypted BOOLEAN DEFAULT false,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 操作日志表
CREATE TABLE IF NOT EXISTS operation_logs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    operation_type VARCHAR(50) NOT NULL,
    table_name VARCHAR(100),
    record_id UUID,
    user_id VARCHAR(100),
    operation_data JSONB,
    ip_address INET,
    user_agent TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- =============================================================================
-- 创建索引
-- =============================================================================

-- 股票表索引
CREATE INDEX IF NOT EXISTS idx_stock_basic_symbol ON stock_basic(symbol);
CREATE INDEX IF NOT EXISTS idx_stock_basic_name ON stock_basic USING gin(name gin_trgm_ops);
CREATE INDEX IF NOT EXISTS idx_stock_basic_industry ON stock_basic(industry);
CREATE INDEX IF NOT EXISTS idx_stock_basic_market ON stock_basic(market);

CREATE INDEX IF NOT EXISTS idx_stock_daily_ts_code ON stock_daily(ts_code);
CREATE INDEX IF NOT EXISTS idx_stock_daily_trade_date ON stock_daily(trade_date);
CREATE INDEX IF NOT EXISTS idx_stock_daily_code_date ON stock_daily(ts_code, trade_date);
CREATE INDEX IF NOT EXISTS idx_stock_daily_sync_status ON stock_daily(sync_status);
CREATE INDEX IF NOT EXISTS idx_stock_daily_quality ON stock_daily(quality_score);

-- 指数表索引
CREATE INDEX IF NOT EXISTS idx_index_basic_name ON index_basic USING gin(name gin_trgm_ops);
CREATE INDEX IF NOT EXISTS idx_index_basic_market ON index_basic(market);
CREATE INDEX IF NOT EXISTS idx_index_basic_category ON index_basic(category);

CREATE INDEX IF NOT EXISTS idx_index_daily_ts_code ON index_daily(ts_code);
CREATE INDEX IF NOT EXISTS idx_index_daily_trade_date ON index_daily(trade_date);
CREATE INDEX IF NOT EXISTS idx_index_daily_code_date ON index_daily(ts_code, trade_date);
CREATE INDEX IF NOT EXISTS idx_index_daily_sync_status ON index_daily(sync_status);

-- 基金表索引
CREATE INDEX IF NOT EXISTS idx_fund_basic_name ON fund_basic USING gin(name gin_trgm_ops);
CREATE INDEX IF NOT EXISTS idx_fund_basic_management ON fund_basic(management);
CREATE INDEX IF NOT EXISTS idx_fund_basic_fund_type ON fund_basic(fund_type);
CREATE INDEX IF NOT EXISTS idx_fund_basic_market ON fund_basic(market);

CREATE INDEX IF NOT EXISTS idx_fund_nav_ts_code ON fund_nav(ts_code);
CREATE INDEX IF NOT EXISTS idx_fund_nav_nav_date ON fund_nav(nav_date);
CREATE INDEX IF NOT EXISTS idx_fund_nav_code_date ON fund_nav(ts_code, nav_date);
CREATE INDEX IF NOT EXISTS idx_fund_nav_sync_status ON fund_nav(sync_status);

-- 债券表索引
CREATE INDEX IF NOT EXISTS idx_bond_basic_name ON bond_basic USING gin(name gin_trgm_ops);
CREATE INDEX IF NOT EXISTS idx_bond_basic_bond_type ON bond_basic(bond_type);
CREATE INDEX IF NOT EXISTS idx_bond_basic_market ON bond_basic(market);
CREATE INDEX IF NOT EXISTS idx_bond_basic_issuer ON bond_basic(issuer);

CREATE INDEX IF NOT EXISTS idx_bond_daily_ts_code ON bond_daily(ts_code);
CREATE INDEX IF NOT EXISTS idx_bond_daily_trade_date ON bond_daily(trade_date);
CREATE INDEX IF NOT EXISTS idx_bond_daily_code_date ON bond_daily(ts_code, trade_date);
CREATE INDEX IF NOT EXISTS idx_bond_daily_sync_status ON bond_daily(sync_status);

-- 系统表索引
CREATE INDEX IF NOT EXISTS idx_sync_tasks_status ON sync_tasks(status);
CREATE INDEX IF NOT EXISTS idx_sync_tasks_next_run ON sync_tasks(next_run_time);
CREATE INDEX IF NOT EXISTS idx_sync_tasks_enabled ON sync_tasks(is_enabled);

CREATE INDEX IF NOT EXISTS idx_data_quality_table ON data_quality_checks(table_name);
CREATE INDEX IF NOT EXISTS idx_data_quality_date ON data_quality_checks(check_date);
CREATE INDEX IF NOT EXISTS idx_data_quality_status ON data_quality_checks(quality_status);

CREATE INDEX IF NOT EXISTS idx_operation_logs_type ON operation_logs(operation_type);
CREATE INDEX IF NOT EXISTS idx_operation_logs_table ON operation_logs(table_name);
CREATE INDEX IF NOT EXISTS idx_operation_logs_created ON operation_logs(created_at);

-- =============================================================================
-- 创建触发器函数
-- =============================================================================

-- 更新时间戳触发器函数
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 版本号更新触发器函数
CREATE OR REPLACE FUNCTION update_version_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.version = OLD.version + 1;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- =============================================================================
-- 创建触发器
-- =============================================================================

-- 股票表触发器
CREATE TRIGGER update_stock_basic_updated_at BEFORE UPDATE ON stock_basic
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_stock_basic_version BEFORE UPDATE ON stock_basic
    FOR EACH ROW EXECUTE FUNCTION update_version_column();

CREATE TRIGGER update_stock_daily_updated_at BEFORE UPDATE ON stock_daily
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_stock_daily_version BEFORE UPDATE ON stock_daily
    FOR EACH ROW EXECUTE FUNCTION update_version_column();

-- 指数表触发器
CREATE TRIGGER update_index_basic_updated_at BEFORE UPDATE ON index_basic
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_index_basic_version BEFORE UPDATE ON index_basic
    FOR EACH ROW EXECUTE FUNCTION update_version_column();

CREATE TRIGGER update_index_daily_updated_at BEFORE UPDATE ON index_daily
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_index_daily_version BEFORE UPDATE ON index_daily
    FOR EACH ROW EXECUTE FUNCTION update_version_column();

-- 基金表触发器
CREATE TRIGGER update_fund_basic_updated_at BEFORE UPDATE ON fund_basic
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_fund_basic_version BEFORE UPDATE ON fund_basic
    FOR EACH ROW EXECUTE FUNCTION update_version_column();

CREATE TRIGGER update_fund_nav_updated_at BEFORE UPDATE ON fund_nav
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_fund_nav_version BEFORE UPDATE ON fund_nav
    FOR EACH ROW EXECUTE FUNCTION update_version_column();

-- 债券表触发器
CREATE TRIGGER update_bond_basic_updated_at BEFORE UPDATE ON bond_basic
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_bond_basic_version BEFORE UPDATE ON bond_basic
    FOR EACH ROW EXECUTE FUNCTION update_version_column();

CREATE TRIGGER update_bond_daily_updated_at BEFORE UPDATE ON bond_daily
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_bond_daily_version BEFORE UPDATE ON bond_daily
    FOR EACH ROW EXECUTE FUNCTION update_version_column();

-- 系统表触发器
CREATE TRIGGER update_sync_tasks_updated_at BEFORE UPDATE ON sync_tasks
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_system_config_updated_at BEFORE UPDATE ON system_config
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- =============================================================================
-- 插入初始数据
-- =============================================================================

-- 插入系统配置
INSERT INTO system_config (config_key, config_value, config_type, description) VALUES
('system.version', '1.0.0', 'string', '系统版本号'),
('sync.enabled', 'true', 'boolean', '数据同步开关'),
('cache.default_ttl', '3600', 'integer', '默认缓存过期时间(秒)'),
('api.rate_limit', '1000', 'integer', 'API速率限制(每小时)'),
('data_quality.threshold', '0.95', 'float', '数据质量阈值'),
('backup.enabled', 'false', 'boolean', '数据备份开关'),
('monitoring.enabled', 'true', 'boolean', '监控开关'),
('log.level', 'INFO', 'string', '日志级别')
ON CONFLICT (config_key) DO NOTHING;

-- 插入默认同步任务
INSERT INTO sync_tasks (task_name, task_type, cron_expression, priority, config) VALUES
('股票基础信息同步', 'stock_basic', '0 0 2 * * *', 'normal', '{"batch_size": 1000, "timeout": 300}'),
('股票日线数据同步', 'stock_daily', '0 */1 * * * *', 'high', '{"batch_size": 500, "timeout": 180}'),
('指数基础信息同步', 'index_basic', '0 0 3 * * *', 'normal', '{"batch_size": 500, "timeout": 300}'),
('指数日线数据同步', 'index_daily', '0 */5 * * * *', 'high', '{"batch_size": 200, "timeout": 180}'),
('基金基础信息同步', 'fund_basic', '0 0 4 * * *', 'normal', '{"batch_size": 1000, "timeout": 600}'),
('基金净值数据同步', 'fund_nav', '0 0 */1 * * *', 'normal', '{"batch_size": 500, "timeout": 300}'),
('债券基础信息同步', 'bond_basic', '0 0 5 * * *', 'low', '{"batch_size": 500, "timeout": 600}'),
('债券日线数据同步', 'bond_daily', '0 0 */6 * * *', 'normal', '{"batch_size": 200, "timeout": 300}'),
('数据质量检查', 'data_quality', '0 0 1 * * *', 'normal', '{"check_all_tables": true}'),
('缓存清理任务', 'cache_cleanup', '0 0 0 * * *', 'low', '{"cleanup_expired": true}')
ON CONFLICT DO NOTHING;

-- =============================================================================
-- 创建视图
-- =============================================================================

-- 股票数据汇总视图
CREATE OR REPLACE VIEW v_stock_summary AS
SELECT 
    sb.ts_code,
    sb.symbol,
    sb.name,
    sb.industry,
    sb.market,
    sd.trade_date,
    sd.close_price,
    sd.pct_chg,
    sd.vol,
    sd.amount,
    sd.quality_score
FROM stock_basic sb
LEFT JOIN stock_daily sd ON sb.ts_code = sd.ts_code
WHERE sb.is_active = true
AND sd.trade_date = (
    SELECT MAX(trade_date) 
    FROM stock_daily 
    WHERE ts_code = sb.ts_code
);

-- 数据质量汇总视图
CREATE OR REPLACE VIEW v_data_quality_summary AS
SELECT 
    table_name,
    check_date,
    AVG(overall_score) as avg_quality_score,
    MIN(overall_score) as min_quality_score,
    MAX(overall_score) as max_quality_score,
    COUNT(*) as check_count,
    SUM(CASE WHEN quality_status = 'excellent' THEN 1 ELSE 0 END) as excellent_count,
    SUM(CASE WHEN quality_status = 'good' THEN 1 ELSE 0 END) as good_count,
    SUM(CASE WHEN quality_status = 'fair' THEN 1 ELSE 0 END) as fair_count,
    SUM(CASE WHEN quality_status = 'poor' THEN 1 ELSE 0 END) as poor_count,
    SUM(CASE WHEN quality_status = 'critical' THEN 1 ELSE 0 END) as critical_count
FROM data_quality_checks
GROUP BY table_name, check_date
ORDER BY check_date DESC, table_name;

-- 同步任务状态视图
CREATE OR REPLACE VIEW v_sync_task_status AS
SELECT 
    task_name,
    task_type,
    status,
    priority,
    last_run_time,
    next_run_time,
    success_count,
    failure_count,
    CASE 
        WHEN success_count + failure_count = 0 THEN 0
        ELSE ROUND(success_count::decimal / (success_count + failure_count) * 100, 2)
    END as success_rate,
    is_enabled,
    CASE 
        WHEN next_run_time < CURRENT_TIMESTAMP AND is_enabled THEN 'overdue'
        WHEN NOT is_enabled THEN 'disabled'
        WHEN status = 'running' THEN 'running'
        ELSE 'scheduled'
    END as current_status
FROM sync_tasks
ORDER BY priority DESC, next_run_time ASC;

-- =============================================================================
-- 创建存储过程
-- =============================================================================

-- 数据质量检查存储过程
CREATE OR REPLACE FUNCTION check_data_quality(p_table_name VARCHAR, p_check_date DATE DEFAULT CURRENT_DATE)
RETURNS TABLE(
    table_name VARCHAR,
    total_records INTEGER,
    quality_score DECIMAL,
    quality_status quality_status,
    issues JSONB
) AS $$
DECLARE
    v_total_records INTEGER;
    v_valid_records INTEGER;
    v_quality_score DECIMAL;
    v_quality_status quality_status;
    v_issues JSONB := '{}';
BEGIN
    -- 根据表名执行不同的质量检查逻辑
    CASE p_table_name
        WHEN 'stock_daily' THEN
            SELECT COUNT(*), COUNT(*) FILTER (WHERE close_price > 0 AND vol >= 0)
            INTO v_total_records, v_valid_records
            FROM stock_daily 
            WHERE trade_date = p_check_date;
            
        WHEN 'index_daily' THEN
            SELECT COUNT(*), COUNT(*) FILTER (WHERE close_price > 0)
            INTO v_total_records, v_valid_records
            FROM index_daily 
            WHERE trade_date = p_check_date;
            
        WHEN 'fund_nav' THEN
            SELECT COUNT(*), COUNT(*) FILTER (WHERE unit_nav > 0)
            INTO v_total_records, v_valid_records
            FROM fund_nav 
            WHERE nav_date = p_check_date;
            
        WHEN 'bond_daily' THEN
            SELECT COUNT(*), COUNT(*) FILTER (WHERE close_price > 0)
            INTO v_total_records, v_valid_records
            FROM bond_daily 
            WHERE trade_date = p_check_date;
            
        ELSE
            v_total_records := 0;
            v_valid_records := 0;
    END CASE;
    
    -- 计算质量分数
    IF v_total_records = 0 THEN
        v_quality_score := 0;
        v_quality_status := 'critical';
        v_issues := jsonb_build_object('error', 'No data found');
    ELSE
        v_quality_score := ROUND(v_valid_records::decimal / v_total_records, 4);
        
        CASE 
            WHEN v_quality_score >= 0.95 THEN v_quality_status := 'excellent';
            WHEN v_quality_score >= 0.90 THEN v_quality_status := 'good';
            WHEN v_quality_score >= 0.80 THEN v_quality_status := 'fair';
            WHEN v_quality_score >= 0.60 THEN v_quality_status := 'poor';
            ELSE v_quality_status := 'critical';
        END CASE;
        
        IF v_quality_score < 1.0 THEN
            v_issues := jsonb_build_object(
                'invalid_records', v_total_records - v_valid_records,
                'invalid_percentage', ROUND((v_total_records - v_valid_records)::decimal / v_total_records * 100, 2)
            );
        END IF;
    END IF;
    
    -- 插入检查结果
    INSERT INTO data_quality_checks (
        table_name, check_date, total_records, valid_records, invalid_records,
        overall_score, quality_status, issues
    ) VALUES (
        p_table_name, p_check_date, v_total_records, v_valid_records, 
        v_total_records - v_valid_records, v_quality_score, v_quality_status, v_issues
    );
    
    -- 返回结果
    RETURN QUERY SELECT p_table_name, v_total_records, v_quality_score, v_quality_status, v_issues;
END;
$$ LANGUAGE plpgsql;

-- =============================================================================
-- 设置权限
-- =============================================================================

-- 创建应用用户（如果不存在）
DO $$
BEGIN
    IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'financial_app') THEN
        CREATE ROLE financial_app LOGIN PASSWORD 'financial_app_pass';
    END IF;
END
$$;

-- 授予权限
GRANT CONNECT ON DATABASE financial_data TO financial_app;
GRANT USAGE ON SCHEMA public TO financial_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO financial_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO financial_app;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO financial_app;

-- 设置默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO financial_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO financial_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO financial_app;

-- =============================================================================
-- 完成初始化
-- =============================================================================

-- 插入初始化完成标记
INSERT INTO system_config (config_key, config_value, config_type, description) VALUES
('database.initialized', 'true', 'boolean', '数据库初始化完成标记'),
('database.init_time', CURRENT_TIMESTAMP::text, 'string', '数据库初始化时间')
ON CONFLICT (config_key) DO UPDATE SET 
    config_value = EXCLUDED.config_value,
    updated_at = CURRENT_TIMESTAMP;

-- 输出初始化完成信息
\echo '========================================'
\echo '金融数据MCP服务数据库初始化完成！'
\echo '========================================'
\echo '数据库名称: financial_data'
\echo '创建表数量: 12'
\echo '创建索引数量: 30+'
\echo '创建视图数量: 3'
\echo '创建存储过程数量: 1'
\echo '初始化时间:' :NOW
\echo '========================================'